package cn.utils;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;

import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;
import org.jdom.xpath.XPath;

public class ImportExcelUtil {

	/**
	 * 根据配置文件xml的，读取导入excel中的数据，组装一个list，返回一个list
	 * @param configName  xml的文件名。注：配置文件只能再class的根目录下
	 * @param excelFile  excel数据源
	 * @param session   传session
	 * @param parameters  某些属性没有从excel中直接读取，需要用变量负值。
	 * @return
	 * @throws Exception
	 * @author mzl
	 * @throws IOException 
	 * @throws JDOMException 
	 */
	public  List  getExcelObjectList(String configName,File excelFile,Map session,Object ...parameters) throws Exception{

		List list = new ArrayList();
		String sheetName="";
		int span=0;
		int row=0;
		try {
			
			SAXBuilder builder = new SAXBuilder();
			//		Document doc=builder.build(this.getClass().getClassLoader().getResourceAsStream("/"+configName)); 
			Document doc = builder.build(this.getClass().getClassLoader()
					.getResourceAsStream("/" + configName));
			Element sheetDom = doc.getRootElement();
			Object beginObj = sheetDom.getAttributeValue("beginRow");
			Object endObj = sheetDom.getAttributeValue("endRow");
			String repeat = sheetDom.getAttributeValue("repeat");
			String increaseObj = sheetDom.getAttributeValue("increase");
			//excel有效值的开始行号
			int beginRow = 0;
			if (null != beginObj) {
				if (!"".equals(beginObj.toString().trim())) {
					beginRow = Integer.parseInt(beginObj.toString());
				}
			}
			//每次循环相隔多少行
			int increase = 1;
			if (null != increaseObj) {
				if (!"".equals(increaseObj.toString().trim())) {
					increase = Integer.parseInt(increaseObj.toString());
				}
			}
			Element entityDom = (Element) XPath.selectSingleNode(sheetDom,
					"entity");
			String entityName = entityDom.getAttributeValue("name");
			List<Element> properties = XPath.selectNodes(sheetDom,
					"/sheet/entity/property");
			
			Workbook workbook = Workbook.getWorkbook(excelFile);
			
			Sheet sheet = workbook.getSheet(Integer.parseInt(sheetDom
					.getAttributeValue("sheetNumber")));
			sheetName=sheet.getName();
			//最后一行
			int endRow;
			//		判断excel数据是否多条重复，当设置为不重复时，让总条数为1，避免迭代
			if ("false".equals(repeat)) {
				endRow = beginRow + 1;
			} else {
				//判断是否设置了最后一行
				if (null == endObj || "".equals(endObj.toString())) {
					endRow = sheet.getRows();
					//System.out.println(sheet.getRows());
				} else {
					endRow = Integer.parseInt(endObj.toString()) + 1;
				}
			}
			for (int i = 0; i < endRow - beginRow; i = i + increase) {
				boolean flag = false;
				Object obj = Class.forName(entityName).newInstance();
				for (Element el : properties) {
					//				获取属性的取值类型
					String type = StringUtil.isAllEmpty(el.getAttributeValue("type"))?"cell":el.getAttributeValue("type").trim().toLowerCase();
					String propery = el.getAttributeValue("name");
					String ignoreNull = el.getAttributeValue("ignoreNull");
					//				读取session值
					if ("session".equals(type)) {
						obj = ObjectUtil.setProperty(obj, propery, session
								.get(el.getAttributeValue("value")));
						//					读取系统时间
					} else if ("date".equals(type)) {
						obj = ObjectUtil.setProperty(obj, propery, new Date());
						//					赋静态值
					} else if ("static".equals(type)) {
						obj = ObjectUtil.setProperty(obj, propery, el
								.getAttributeValue("value"));
						//					读取excel值
					} else if ("variable".equals(type)) {
						int index = Integer.parseInt(el
								.getAttributeValue("value"));
						obj = ObjectUtil.setProperty(obj, propery,parameters[index]);
					} else {
						Element cellDom = ((Element) XPath.selectSingleNode(el,
								"cell"));
						String cellText = cellDom.getText();
						//					非空是读取excel表格值
						if (!StringUtil.isAllEmpty(cellText)) {
							String arr[] = cellText.split(",");
							span = Integer.parseInt(arr[1]);
							row = Integer.parseInt(arr[0]);
							//判断是固定行还是循环读取
							if (!"static".equals(cellDom
									.getAttributeValue("type"))) {
								row = i + row;
							}
							Object cellVal = null;
							Cell cell = sheet.getCell(span, row);
							//判断单元格是非空
							if (!"".equals(cell.getContents().trim())) {
								//							判断是否设置为忽略其非空
								if (!"true".equals(ignoreNull)) {
									flag = true;
								}
								if (cell.getType().toString().equals("Date")) {
									if (!"".equals(cell.getContents().trim())) {
										cellVal = ((DateCell) cell).getDate();
									}
									obj = ObjectUtil.setProperty(obj, propery,
											cellVal);
								} else {
									cellVal = cell.getContents();
									if (arr.length > 2 && null != cellVal
											&& !"".equals(cellVal)) {
										int index = Integer.parseInt(arr[2]);
										String arrVal[] = cellVal.toString()
												.split("_");
										obj = ObjectUtil.setProperty(obj,
												propery, arrVal[index]);
									} else {
										obj = ObjectUtil.setProperty(obj,
												propery, cellVal);
									}
								}
							}
						}
					}
				}
				if (flag) {
					list.add(obj);
				} else {
					break;
				}
			}
		} catch (Exception e) {
			
			e.printStackTrace();
			Logger logger=Logger.getLogger("errorLog");
			logger.error(sheetName+"的第"+(row+1)+"行,第"+(span+1)+"列数据不对");
			logger.error(e);
			throw new Exception(sheetName+"的第"+(row+1)+"行,第"+(span+1)+"列数据不对");
		}
	   return list;
	}
	
}
